MouseUp/MouseDown Events
James Kellndorfer
I can use the Macro recorder and it provides the X,Y coordinates as a single
data type. These values unfortunately can't change as the chart's data set
changes. I am looking to add lines programmatically.
I am trying to understand the X,Y Long values from the MouseUp/MouseDown
events. How can I convert these coordinates to match the plotarea's
coordinates? The ranges of values for the mouse and the plotarea are
different. In addition, when you zoom the chart the plotarea's range remain
the same but the mouse's range of values is different.
Again, the macro recorder can get the plotarea's coordinates but I can't
find the object which produces these same values with a mouse click.
jameskel
jameskel
Tushar Mehta
The data does not have to be in a worksheet range. You can always
create a plot from =SERIES(,{1,1},{1,2},2) to create a vertical line.
And, yes, you can do this programmatically. Turn on the recorder (Tools
| Macro > Record New Macro...), add the series through the GUI, turn
off the recorder, and use the resulting code as a starting point.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <kM7a7.13$q44....@news1.news.adelphia.net>, James Kellndorfer
<jame...@erols.com> wrote
jameskel
the process of drawing additional lines or at least use one mouse click to
draw a line of a pre-defined length.
I can draw a straight line and superimpose it on the chart manually but
converting MouseX MouseY so that the Shapes.AddLine object can create a line
with a click eludes me.
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.15d502b28...@msnews.microsoft.com...
jameskel
the process of drawing additional lines or at least use one mouse click to
draw a line of a pre-defined length.
I can draw a straight line and superimpose it on the chart manually but
converting MouseX MouseY so that the Shapes.AddLine object can create a line
with a click eludes me.
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.15d502b28...@msnews.microsoft.com...
David Lee
corner of the chart "window". Excel doesn't have any other functionality
using Client Coordinates although you can get at them via the API. I tried
it and got in rather a tangle trying to find a handle for the "window"!
However Stephen Bullen came up with a procedure for converting the X, Y
values to coordinate values based on the chart axes. Look for the thread
on the group microsoft.public.excel.programming titled: Excel API question:
"is it possible .....?" (Stephen's post was on 20th July).
Results weren't perfectly accurate - Stephen reckoned them to be within a
pixel when he tried it but on my machine the results could be out by up to
4 minimum mouse increments. Accurate enough for most purposes though.
James Kellndorfer <jame...@erols.com> wrote in article
<kM7a7.13$q44....@news1.news.adelphia.net>...
> I am trying to draw lines on a chart.
>
> I can use the and it provides the X,Y coordinates as a single
jameskel
Did you use the ActiveChart.GetChartElement in an effort to obtain the
proper Window handle?
Just curious if you used this statement in your quest.
Isn't it interesting that the MACRO RECORDER can get these values but we
can't.
"David Lee" <dlee_m...@hotmail.com> wrote in message
news:01c08c65$6004b280$d00a5092@DAVID_LEE...
jameskel
I went back as far as my service will allow. My messages stop @ 7/22/01
Could I trouble you to cut and paste this thread?
Or is there a way to override my systems limit of 7/22/01?
I use Outlook Express so I used the tools to "Get the Next 300 Headers"
repeatedly.
"David Lee" <dlee_m...@hotmail.com> wrote in message
news:01c08c65$6004b280$d00a5092@DAVID_LEE...
Tushar Mehta
--
Regards,
Tushar Mehta
www.tushar-mehta.com
--
In <emeb7.264$Sy.4...@news1.news.adelphia.net>, jameskel
<jame...@adelphia.net> wrote
David Lee
Excel generates a single window that it uses for editing any chart and you
can get a handle to this.
I managed to convert the X,Y coordinates from Mousedown to chart axis
coordinates by obtaining the size of the client window using GetClientRect
(from the API) and using the size of the window and the x and y axis
positions and lengths obtained from Excel. However Stephen Bullen's
approach gave better accuracy. You need to understand what the various
chart area definitions actually represent and I'm not absolutely sure that
I do!
You'll find more details on the thread that I pointed you at. Find it
using Google - do an advanced search on Groups.
jameskel <jame...@adelphia.net> wrote in article
<c%db7.257$Sy.4...@news1.news.adelphia.net>...
jameskel
the code:
And It didn't work. By that I mean Mr. Bullen's calculations to convert the
Mouse coordinates to XY values weren't even close.
So if it worked for you, then I have to conclude that something is wrong
with my copy Office97 or at least the interaction with objects is messed up.
Perhaps there is a way to "SPY" the Macro Recorder and find out which
objects are at work because the Macro Recorder gets the exact ChartArea
Values.
Plus if you change the magnification you can't reference the scroll bars The
Mouse XY is always the same on the screen (if you pick the same spot on the
screen). But the point on the chart isn't.
I must be dense. I've tried scaling the window, etc. All kinds of APIs. But
I can't get the ChartArea's coordinates(points). I'm not trying to get the
Chart's XY values(the chart's representation of the data).
For the benefit of this group here's a edited recap of the thread...
The class name for an embedded chart is EXCELE.
'Get the handle on Excel's main window
hwnd = FindWindow("XLMAIN", Application.Caption)
'Get the handle on Excel's desktop
hWndDesk = FindWindowEx(hwnd, 0, "XLDESK", vbNullString)
'Get the handle for an embedded chart
hWndChart = FindWindowEx(hWndDesk, 0, "EXCELE", vbNullString)
You should be able to then call GetClientRect with hWndChart as
the window handle:
Using your original code:
err = GetClientRect(hWndChart,winRect)
In your original posting, you declared the user defined data type
RECT as:
Private Type RECT
Left As Integer
Top As Integer
Right As Integer
Bottom As Integer
End Type
This is incorrect. The data types must be Long not Integer.
Unfortunately, I do not think this is going to help you with
translating the client coordinates as returned from a MouseDown event
into "Chart coordinates". What you really need is the coordinates of
the plot area (specifically left top, left bottom, right bottom in
client coordinates). You were probably thinking you could ratio the
height and width of the chart object and use that relationship to
convert the Plot Area coordinates to client coordinates. It does not
work.
I setup a sample worksheet where I defined a chart, enabled chart
events, and using a crosshair cursor for precision, extracted various
client coordinates from the chart. The inside top property was defined
as 19 points. The same location in client coordinates is 30 pixels. If
you try and calculate this value you get about 25 pixels using the ratio
of object height in points to object height in client coordinates.
What we need is a coordinates transformation function which will work
no matter what the current scale or scroll position. Whether this
is possible given what we have to work with is still unknown.
These are just my thoughts and I am by no means an expert. Hopefully
we can figure this out once and for all.
John Chauvin
It seems to me to be the editing window that Excel uses when an embedded
chart is activated (which may be exactly what Dave wants!). It provides
the drag handles for the chart, and the thick border and caption if you've
chosen the 'View in own window' option.
To find the EXCELE handle, it's easiest to drill down from XLMAIN, using
FindWindowEx:
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Sub test()
Dim hWndXL As Long, hWndDesk As Long, hWndXLE As Long
hWndXL = FindWindow("XLMAIN", Application.Caption)
hWndDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
hWndXLE = FindWindowEx(hWndDesk, 0&, "EXCELE", vbNullString)
End Sub
Regards
Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk or http://208.49.24.208
This conversion depends on a number of factors, as you've already
discovered:
- The worksheet zoom factor (impacting the conversion of pixels to points)
- Whether showing large or small fonts (impacting the conversion of pixels
to points)
- The differing coordinate systems of the X,Y numbers and the chart item's
dimensions 0,0 is top-left in mouse coords but bottom-left in chart coords.
- The differing origins of the coordinate systems. 0,0 in mouse coords is
the top-left of the chart area (a few pixels inside the window), while (0,0)
in chart coords is the bottom-left of the inside of the plot area.
Given the above, the following seems to provide +/- 1 pixel accuracy in
most settings I've tested it in (hope the word wrapping is OK):
'API's for getting the factors to convert points to pixels
Private Declare Function GetDC Lib "user32" ( _
ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "Gdi32" ( _
ByVal hDC As Long, _
ByVal nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" ( _
ByVal hwnd As Long, _
ByVal hDC As Long) As Long
Private Const LOGPIXELSX = 88
Private Const LOGPIXELSY = 90
Public WithEvents oChart As Chart
Private Sub oChart_MouseMove(ByVal Button As Long, ByVal Shift As Long,
ByVal X As Long, ByVal Y As Long)
Dim dZoom As Double
Dim dMin As Double
Dim dMax As Double
dZoom = ActiveWindow.Zoom / 100
With oChart
dMin = .Axes(xlCategory).MinimumScale
dMax = .Axes(xlCategory).MaximumScale
xVal = dMin + (dMax - dMin) * ((X - IIf(dZoom > 1, 6 * (dZoom - 1),
0)) * PointsPerPixelX / dZoom - _
(.PlotArea.InsideLeft +
.ChartArea.Left)) / .PlotArea.InsideWidth
dMin = .Axes(xlValue).MinimumScale
dMax = .Axes(xlValue).MaximumScale
yVal = dMin + (dMax - dMin) * (1 - ((Y - IIf(dZoom > 1, 6 * (dZoom -
1), 0)) * PointsPerPixelY / dZoom - _
(.PlotArea.InsideTop +
.ChartArea.Top)) / .PlotArea.InsideHeight)
End With
Application.StatusBar = "(" & Application.Round(xVal, 2) & ", " &
Application.Round(yVal, 2) & ")"
End Sub
'The width of a pixel in Excel's userform coordinates
Public Property Get PointsPerPixelX() As Double
Dim hDC As Long
hDC = GetDC(0)
'A point is defined as 1/72 of an inch and LOGPIXELSX returns
'the number of pixels per logical inch, so divide them to give
'the width of a pixel in Excel's userform coordinates
PointsPerPixelX = 72 / GetDeviceCaps(hDC, LOGPIXELSX)
ReleaseDC 0, hDC
End Property
'The width of a pixel in Excel's userform coordinates
Public Property Get PointsPerPixelY() As Double
Dim hDC As Long
hDC = GetDC(0)
'A point is defined as 1/72 of an inch and LOGPIXELSX returns
'the number of pixels per logical inch, so divide them to give
'the width of a pixel in Excel's userform coordinates
PointsPerPixelY = 72 / GetDeviceCaps(hDC, LOGPIXELSY)
ReleaseDC 0, hDC
End Property
Regards
Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk or http://208.49.24.208
> The only problem I have noticed is the Y coordinate becomes
> extremely large if the chart is partially scrolled out of view.
Left, right, top or bottom?
> I do have a
> few questions:
>
> How did you come up with the constants LOGPIXELSX and LOGPIXELSY?
They're defined in the 'Windows API Viewer' tool that comes with the
Developer editions of Office and VB etc.
> Can you explain exactly what the theory is behind the equations for
> xVal and yVal?
Sure:
The X and Y values coming in to the event are pixel coordinates, with an
origin at the top-left of the ChartArea.
The data plotted on that chart are in coordinates defined by the Category
and
Value axes (assuming an XY chart), where the bit displayed on the chart is
the area between the axes minimum and maximum values and where the origin is
the bottom-left corner of the inside of the plot area (unless the 'values in
reverse order' check box is ticked on either axis, in which the origin could
be in any corner - but I ignored that complexity!)
To convert from one coordinate system (pixels on the screen) to another
(data
in the chart), we first need to decide on a common measurement system. I
chose to use Points, as that is the measurement system used by the chart
objects (ChartArea, PlotArea) etc.
So, the conversion goes something like this:
1. We're given the cursor position in pixels, X
2. Convert that to points, using the PointsPerPixel functions, adjusting
for
different display Zoom factors, (X * PointerPerPixelX / dZoom)
3. At >100% zoom, the result seemed to drift a little, so I added an
adjustment to counteract that, IIf(dZoom > 1, 6 * (dZoom - 1)) *
PointsPerPixel / dZoom
NB. This may not be completely accurate, but seems OK in testing
4. I now have the cursor position in points.
5. Subtract the PlotArea's InsideLeft amount and the ChartArea's Left
amount
to find the gap between the left of the inside plot area and the cursor
6. Take the ratio of that gap to the PlotArea's InsideWidth and apply it to
the visible range of the axes scaling.
7. That gives me the data-coordinate value for the cursor's position.
Regards
Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk or http://208.49.24.208
|"Stephen Bullen" <Ste...@BMSLtd.ie> wrote
|Hi John,
|
|> The only problem I have noticed is the Y coordinate becomes
|> extremely large if the chart is partially scrolled out of view.
|
|Left, right, top or bottom?
Initialize the chart, scroll the embedded chart so that the top is
not visible in on the Excel desktop. Select the chart, moving the
mouse shows a large Y coordinate in the status bar display.
A similar phenomenon occurs if the left side of the chart is
scrolled off resulting in a large negative X coordinate value. Both
are of the form 114532####.## on a 1024x768 display XL2000,
on Win98SE.
The readings are normal when the right and/or the bottom of the
chart is not visible due to scrolling.
-- Sam
Thanks
It looks like there's a bug in .ChartArea.Left and .ChartArea.Top -
they're probably using unsigned longs behind the scenes. We just need
to detect it and change it back (hope the word-wrap's OK):
Private Sub oChart_MouseMove(ByVal Button As Long, ByVal Shift As Long,
ByVal X As Long, ByVal Y As Long)
Dim dZoom As Double
Dim dMin As Double
Dim dMax As Double
Dim dChartLeft As Double
Dim dChartTop As Double
dZoom = ActiveWindow.Zoom / 100
With oChart
dChartLeft = .ChartArea.Left
If dChartLeft > 2 ^ 31 Then
dChartLeft = dChartLeft - 2 ^ 32
End If
dChartTop = .ChartArea.Top
If dChartTop > 2 ^ 31 Then
dChartTop = dChartTop - 2 ^ 32
End If
dMin = .Axes(xlCategory).MinimumScale
dMax = .Axes(xlCategory).MaximumScale
xVal = dMin + (dMax - dMin) * ((X - IIf(dZoom > 1, 6 * (dZoom - 1),
0)) * PointsPerPixelX / dZoom - _
(.PlotArea.InsideLeft + dChartLeft)) / .PlotArea.InsideWidth
dMin = .Axes(xlValue).MinimumScale
dMax = .Axes(xlValue).MaximumScale
yVal = dMin + (dMax - dMin) * (1 - ((Y - IIf(dZoom > 1, 6 * (dZoom -
1), 0)) * PointsPerPixelY / dZoom - _
(.PlotArea.InsideTop + dChartTop)) / .PlotArea.InsideHeight)
End With
Application.StatusBar = "(" & Application.Round(xVal, 2) & ", " &
Application.Round(yVal, 2) & ")"
End Sub
Regards
Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk or http://208.49.24.208
Hi John,
> Yet this does not work, for example, you have to take the sum of the
> ChartTop plus PlotAreaInsideTop to get a compariable measure to that
> represented by the client coordinates. Why
When you draw a chart on a worksheet, you get a ChartObject, which has
Top and Left properties based on the worksheet (0,0 is top-left of cell
A1).
Inside a ChartObject there is a small border, then the ChartArea, so
ChartArea.Top and .Left are 12 points or so - the size of that border.
Inside the ChartArea is the PlotArea (which includes the axis labels),
inside of which is the areae actually drawn on. 0,0 of the InsideLeft
and InsideTop is the top-left of the ChartArea, while 0,0 of the X and
Y values given in the charts mouse events is the top-left of the
ChartObject (view in a fixed-width font):
1------------------------------------------+
|ChartObject |
| 2--------------------------------------+ |
| |ChartArea | |
| | | |
| | +--------------------------------+ | |
| | |PlotArea | | |
| | | 20 +-----------------------+| | |
| | | | 'Inside' dimensions || | |
| | | 10 | || | |
| | | | || | |
| | | 0 +-----------------------+| | |
| | +--------------------------------+ | |
| +--------------------------------------+ |
+------------------------------------------+
Point 1 is the origin for X and Y values passed to chart mouse events.
Point 2 is the original for the PlotArea InsideLeft and InsideTop.
The difference is the ChartArea.Left and ChartArea.Top
Regards
Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk or http://208.49.24.208
Excellent! Thanks very much for your solution.
Is your adjustment IIf(dZoom > 1, 6 * (dZoom - 1)) * PointsPerPixel / dZoom
based on trial and error or some calculation? On my machine (running
Office 97 under NT4 SP6) the accuracy is not quite as good as you found.
Using the Accessibility Options to nudge a cross hairs cursor I find that
the values returned can be off by up to 4 mouse increments and I wonder if
fine tuning may further improve this? Even so the accuracy and stability
are very much better than I was getting using my solution (using
GetClientRect to calibrate the x,y coordinates).
Now you've shown us what to look for I've come across a demo at
http://www.mvps.org/vbnet/index.html?code/screen/displaysettings.htm
that includes a listing of the values of the constants used in
GetDeviceCaps. Could be helpful if anyone wants to experiment further.
Many thanks for your time.
David
Hi David,
> Is your adjustment IIf(dZoom > 1, 6 * (dZoom - 1)) * PointsPerPixel /
dZoom
> based on trial and error or some calculation?
100% Trial and error, and not too much of it either <g>
Regards
Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk or http://208.49.24.208
"David Lee" <dlee_m...@hotmail.com> wrote in message
news:01c11e84$e90af3d0$d00a5092@DAVID_LEE...